package database

import (
	"context"
	"database/sql"
	"fmt"
	"strconv"
	"strings"
	"time"

	"github.com/pkg/errors"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"

	"changeme/internal/models"
)

// PostgreSQLDriver PostgreSQL数据库驱动
type PostgreSQLDriver struct {
	BaseDriver
	gormDB *gorm.DB
}

// NewPostgreSQLDriver 创建PostgreSQL驱动实例
func NewPostgreSQLDriver() *PostgreSQLDriver {
	return &PostgreSQLDriver{
		BaseDriver: BaseDriver{
			Dialect: &PostgreSQLDialect{},
		},
	}
}

// Connect 连接到PostgreSQL数据库
func (p *PostgreSQLDriver) Connect(config *models.DatabaseConfig) error {
	if p.Connected {
		return ErrConnectionExists
	}

	// 构建连接字符串
	dsn := fmt.Sprintf("host=%s port=%d user=%s password=%s",
		config.Host, config.Port, config.Username, config.Password)

	// 添加数据库名称（如果提供）
	if config.Database != "" {
		dsn += fmt.Sprintf(" dbname=%s", config.Database)
	}

	// 添加SSL模式
	if config.SSLMode != "" {
		dsn += fmt.Sprintf(" sslmode=%s", config.SSLMode)
	} else {
		dsn += " sslmode=disable" // 默认禁用SSL
	}

	// 添加其他选项
	if config.Options != "" {
		dsn += " " + config.Options
	}

	// 配置GORM
	gormConfig := &gorm.Config{
		Logger: logger.Default.LogMode(logger.Silent),
	}

	// 使用GORM连接
	gormDB, err := gorm.Open(postgres.Open(dsn), gormConfig)
	if err != nil {
		return errors.Wrap(err, "failed to open postgresql connection with GORM")
	}

	// 获取底层的sql.DB对象
	sqlDB, err := gormDB.DB()
	if err != nil {
		return errors.Wrap(err, "failed to get underlying sql.DB from GORM")
	}

	// 设置连接池参数
	sqlDB.SetMaxOpenConns(25)
	sqlDB.SetMaxIdleConns(5)
	sqlDB.SetConnMaxLifetime(5 * time.Minute)

	// 测试连接
	if err := sqlDB.Ping(); err != nil {
		sqlDB.Close()
		return errors.Wrap(err, "failed to ping postgresql database")
	}

	p.DB = sqlDB
	p.gormDB = gormDB
	p.Config = config
	p.Connected = true

	return nil
}

// GetVersion 获取PostgreSQL版本
func (p *PostgreSQLDriver) GetVersion() (string, error) {
	if !p.IsConnected() {
		return "", ErrNotConnected
	}

	var version string
	err := p.DB.QueryRow("SELECT version()").Scan(&version)
	if err != nil {
		return "", errors.Wrap(err, "failed to get postgresql version")
	}

	return version, nil
}

// ListDatabases 列出所有数据库
func (p *PostgreSQLDriver) ListDatabases(ctx context.Context) ([]models.DatabaseInfo, error) {
	if !p.IsConnected() {
		return nil, ErrNotConnected
	}

	query := `
		SELECT 
			datname as name,
			pg_encoding_to_char(encoding) as charset,
			datcollate as collation,
			pg_size_pretty(pg_database_size(datname)) as size,
			description
		FROM pg_database
		LEFT JOIN pg_description ON pg_database.oid = pg_description.objoid
		WHERE datistemplate = false
		ORDER BY datname
	`

	rows, err := p.DB.QueryContext(ctx, query)
	if err != nil {
		return nil, errors.Wrap(err, "failed to list databases")
	}
	defer rows.Close()

	var databases []models.DatabaseInfo
	for rows.Next() {
		var db models.DatabaseInfo
		var sizeStr, description sql.NullString
		if err := rows.Scan(&db.Name, &db.Charset, &db.Collation, &sizeStr, &description); err != nil {
			return nil, errors.Wrap(err, "failed to scan database info")
		}

		if description.Valid {
			db.Comment = description.String
		}

		// 尝试解析大小字符串（如 "123 MB"）
		if sizeStr.Valid {
			db.Size = -1 // 表示大小字符串
		}

		databases = append(databases, db)
	}

	return databases, nil
}

// ListTables 列出指定数据库的所有表
func (p *PostgreSQLDriver) ListTables(ctx context.Context, database string) ([]models.TableInfo, error) {
	if !p.IsConnected() {
		return nil, ErrNotConnected
	}

	// PostgreSQL没有USE语句，我们需要确保连接到正确的数据库
	if p.Config.Database != database {
		return nil, errors.New("connected to different database, reconnect required")
	}

	query := `
		SELECT 
			c.relname as name,
			n.nspname as schema_name,
			CASE c.relkind 
				WHEN 'r' THEN 'TABLE' 
				WHEN 'v' THEN 'VIEW' 
				WHEN 'm' THEN 'MATERIALIZED VIEW' 
				WHEN 'f' THEN 'FOREIGN TABLE' 
				ELSE c.relkind::text 
			END as type,
			'postgres' as engine,
			coll.collname as collation,
			c.reltuples as row_count,
			pg_relation_size(c.oid) as data_length,
			pg_indexes_size(c.oid) as index_length,
			d.description as comment,
			to_char(COALESCE(
				pg_stat_file('base/' || c.relfilenode::text || '/VM').modification,
				pg_stat_file('base/' || c.relfilenode::text).modification
			), 'YYYY-MM-DD HH24:MI:SS') as created_at
		FROM pg_class c
		JOIN pg_namespace n ON n.oid = c.relnamespace
		LEFT JOIN pg_collation coll ON coll.oid = c.relcollation
		LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = 0
		WHERE c.relkind IN ('r','v','m','f')
		AND n.nspname NOT IN ('pg_catalog', 'information_schema')
		ORDER BY c.relname
	`

	rows, err := p.DB.QueryContext(ctx, query)
	if err != nil {
		return nil, errors.Wrap(err, "failed to list tables")
	}
	defer rows.Close()

	var tables []models.TableInfo
	for rows.Next() {
		var table models.TableInfo
		var createdAt, comment, collation sql.NullString
		if err := rows.Scan(
			&table.Name, &table.Schema, &table.Type, &table.Engine,
			&collation, &table.RowCount, &table.DataLength,
			&table.IndexLength, &comment, &createdAt,
		); err != nil {
			return nil, errors.Wrap(err, "failed to scan table info")
		}

		if comment.Valid {
			table.Comment = comment.String
		}

		if collation.Valid {
			table.Collation = collation.String
		}

		if createdAt.Valid {
			table.CreatedAt = createdAt.String
		}

		tables = append(tables, table)
	}

	return tables, nil
}

// GetTableColumns 获取表的列信息
func (p *PostgreSQLDriver) GetTableColumns(ctx context.Context, database, table string) ([]models.ColumnInfo, error) {
	if !p.IsConnected() {
		return nil, ErrNotConnected
	}

	// 尝试获取表所属的schema
	var schema string
	if strings.Contains(table, ".") {
		parts := strings.Split(table, ".")
		schema = parts[0]
		table = parts[1]
	} else {
		schema = "public" // 默认schema
	}

	// PostgreSQL查询列信息
	query := `
		SELECT 
			a.attname as column_name,
			pg_catalog.format_type(a.atttypid, a.atttypmod) as data_type,
			CASE 
				WHEN a.atttypmod > 0 AND a.atttypid IN (1042, 1043) THEN a.atttypmod - 4 -- char/varchar
				WHEN a.atttypmod > 0 AND a.atttypid IN (1560, 1562) THEN (a.atttypmod >> 16) & 65535 -- bit/varbit
				ELSE NULL
			END as char_length,
			CASE 
				WHEN a.atttypid IN (21, 23, 20, 700, 701, 1700) THEN 
					(
						SELECT CASE WHEN pg_attribute.atttypmod = -1
							THEN NULL
							ELSE (((pg_attribute.atttypmod - 4) >> 16) & 65535)
						END
						FROM pg_attribute
						WHERE pg_attribute.attrelid = a.attrelid AND pg_attribute.attname = a.attname
					)
				ELSE NULL
			END as numeric_precision,
			CASE 
				WHEN a.atttypid IN (21, 23, 20, 700, 701, 1700) THEN 
					(
						SELECT CASE WHEN pg_attribute.atttypmod = -1
							THEN NULL
							ELSE ((pg_attribute.atttypmod - 4) & 65535)
						END
						FROM pg_attribute
						WHERE pg_attribute.attrelid = a.attrelid AND pg_attribute.attname = a.attname
					)
				ELSE NULL
			END as numeric_scale,
			CASE WHEN a.attnotnull THEN 'NO' ELSE 'YES' END as is_nullable,
			CASE WHEN pk.contype IS NOT NULL THEN 'PRI' ELSE '' END as column_key,
			pg_catalog.col_description(a.attrelid, a.attnum) as column_comment,
			a.attnum as ordinal_position,
			COALESCE(
				pg_get_expr(def.adbin, def.adrelid),
				null
			) as column_default,
			CASE 
				WHEN a.attidentity <> '' THEN 'auto_increment' 
				WHEN pg_get_expr(def.adbin, def.adrelid) LIKE 'nextval%' THEN 'auto_increment'
				ELSE '' 
			END as extra,
			NULL as character_set_name,
			NULL as collation_name,
			pg_catalog.format_type(a.atttypid, a.atttypmod) as full_type
		FROM pg_catalog.pg_attribute a
		LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid = def.adrelid AND a.attnum = def.adnum)
		LEFT JOIN (
			SELECT conrelid, conkey, contype 
			FROM pg_catalog.pg_constraint 
			WHERE contype = 'p'
		) pk ON (a.attrelid = pk.conrelid AND a.attnum = ANY(pk.conkey))
		WHERE a.attrelid = (
			SELECT c.oid 
			FROM pg_catalog.pg_class c
			LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
			WHERE c.relname = $1 AND n.nspname = $2
		)
		AND a.attnum > 0
		AND NOT a.attisdropped
		ORDER BY a.attnum
	`

	rows, err := p.DB.QueryContext(ctx, query, table, schema)
	if err != nil {
		return nil, errors.Wrap(err, "failed to get table columns")
	}
	defer rows.Close()

	var columns []models.ColumnInfo
	for rows.Next() {
		var column models.ColumnInfo
		var columnName, dataType, isNullable, columnKey, comment, fullType sql.NullString
		var charLength, numPrecision, numScale, position sql.NullInt64
		var columnDefault, extra, charSet, collation sql.NullString

		// 扫描所有字段
		if err := rows.Scan(
			&columnName, &dataType, &charLength, &numPrecision, &numScale,
			&isNullable, &columnKey, &comment, &position, &columnDefault,
			&extra, &charSet, &collation, &fullType,
		); err != nil {
			return nil, errors.Wrap(err, "failed to scan column info")
		}

		// 整理类型信息
		colType := dataType.String
		// 提取基本类型（去掉长度信息）
		if strings.Contains(colType, "(") {
			colType = colType[:strings.Index(colType, "(")]
		}

		// 设置列信息
		column.Name = columnName.String
		column.Type = strings.ToLower(colType)
		column.FullType = fullType.String

		// 处理长度、精度和小数位
		if charLength.Valid {
			column.Length = int(charLength.Int64)
		}
		if numPrecision.Valid {
			column.Precision = int(numPrecision.Int64)
		}
		if numScale.Valid {
			column.Scale = int(numScale.Int64)
		}

		// 处理NULL约束
		column.IsNullable = isNullable.String == "YES"
		column.NotNull = !column.IsNullable

		// 处理主键
		column.IsPrimaryKey = columnKey.String == "PRI"
		column.PrimaryKey = column.IsPrimaryKey

		// 处理自增
		column.IsAutoIncrement = strings.Contains(strings.ToLower(extra.String), "auto_increment") ||
			strings.Contains(strings.ToLower(fullType.String), "serial")
		column.AutoIncrement = column.IsAutoIncrement

		// 处理默认值
		if columnDefault.Valid {
			column.DefaultValue = columnDefault.String
		}

		// 其他元数据
		column.Comment = comment.String
		column.Position = int(position.Int64)
		column.Charset = charSet.String
		column.Collation = collation.String
		column.Extra = extra.String

		// 处理特殊类型（枚举等）
		if strings.HasPrefix(column.FullType, "ARRAY") ||
			strings.HasPrefix(column.FullType, "array") {
			column.Type = "array"
		} else if strings.HasPrefix(column.FullType, "json") ||
			strings.HasPrefix(column.FullType, "JSON") {
			column.Type = "json"
		} else if strings.HasPrefix(column.FullType, "jsonb") ||
			strings.HasPrefix(column.FullType, "JSONB") {
			column.Type = "jsonb"
		}

		column.RawInfo = map[string]interface{}{
			"columnName": columnName.String,
			"dataType":   dataType.String,
			"fullType":   fullType.String,
			"isNullable": isNullable.String,
			"columnKey":  columnKey.String,
			"default":    columnDefault.String,
		}

		columns = append(columns, column)
	}

	// 使用格式化工具处理列信息
	return FormatColumns(columns, models.PostgreSQL), nil
}

// GetTableIndexes 获取表的索引信息
func (p *PostgreSQLDriver) GetTableIndexes(ctx context.Context, database, table string) ([]models.IndexInfo, error) {
	if !p.IsConnected() {
		return nil, ErrNotConnected
	}

	// PostgreSQL没有USE语句，我们需要确保连接到正确的数据库
	if p.Config.Database != database {
		return nil, errors.New("connected to different database, reconnect required")
	}

	// 尝试获取表所属的schema
	var schema string
	if strings.Contains(table, ".") {
		parts := strings.Split(table, ".")
		schema = parts[0]
		table = parts[1]
	} else {
		schema = "public" // 默认schema
	}

	query := `
		SELECT
			i.relname as name,
			am.amname as type,
			ix.indisunique as is_unique,
			ix.indisprimary as is_primary,
			array_to_string(array_agg(a.attname ORDER BY k.i), ',') as columns,
			d.description as comment
		FROM
			pg_index ix
			JOIN pg_class i ON i.oid = ix.indexrelid
			JOIN pg_class t ON t.oid = ix.indrelid
			JOIN pg_namespace n ON n.oid = t.relnamespace
			JOIN pg_am am ON am.oid = i.relam
			LEFT JOIN pg_description d ON d.objoid = i.oid
			LEFT JOIN LATERAL unnest(ix.indkey) WITH ORDINALITY AS k(attnum, i) ON TRUE
			LEFT JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = k.attnum
		WHERE
			t.relname = $1
			AND n.nspname = $2
		GROUP BY
			i.relname, am.amname, ix.indisunique, ix.indisprimary, d.description
		ORDER BY
			i.relname
	`

	rows, err := p.DB.QueryContext(ctx, query, table, schema)
	if err != nil {
		return nil, errors.Wrap(err, "failed to get table indexes")
	}
	defer rows.Close()

	var indexes []models.IndexInfo
	for rows.Next() {
		var index models.IndexInfo
		var columnsStr string
		var comment sql.NullString
		if err := rows.Scan(
			&index.Name, &index.Type, &index.IsUnique,
			&index.IsPrimary, &columnsStr, &comment,
		); err != nil {
			return nil, errors.Wrap(err, "failed to scan index info")
		}

		index.Columns = strings.Split(columnsStr, ",")

		if comment.Valid {
			index.Comment = comment.String
		}

		indexes = append(indexes, index)
	}

	return indexes, nil
}

// ExecuteQuery 执行查询语句
func (p *PostgreSQLDriver) ExecuteQuery(ctx context.Context, query string, args ...interface{}) (*models.QueryResult, error) {
	if !p.IsConnected() {
		return nil, ErrNotConnected
	}

	start := time.Now()
	rows, err := p.DB.QueryContext(ctx, query, args...)
	if err != nil {
		return &models.QueryResult{
			Error:       err.Error(),
			ExecuteTime: time.Since(start),
		}, nil
	}
	defer rows.Close()

	// 获取列名
	columns, err := rows.Columns()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get columns")
	}

	// 获取列类型
	colTypes, err := rows.ColumnTypes()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get column types")
	}

	// 读取数据
	var result []map[string]interface{}
	for rows.Next() {
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))
		for i := range values {
			valuePtrs[i] = &values[i]
		}

		if err := rows.Scan(valuePtrs...); err != nil {
			return nil, errors.Wrap(err, "failed to scan row")
		}

		row := make(map[string]interface{})
		for i, col := range columns {
			val := values[i]

			// 处理特殊类型
			if val != nil {
				// 处理byte数组
				if b, ok := val.([]byte); ok {
					// 尝试确定数据类型
					if colTypes != nil && i < len(colTypes) {
						// 检查数据库列类型
						dbType := strings.ToLower(colTypes[i].DatabaseTypeName())

						// 对于BIGINT类型，转换为字符串以避免JS中的整数溢出
						if strings.Contains(dbType, "int8") {
							row[col] = string(b)
							continue
						}

						// 对于DECIMAL/NUMERIC类型，也转换为字符串保留精度
						if strings.Contains(dbType, "numeric") || strings.Contains(dbType, "decimal") {
							row[col] = string(b)
							continue
						}
					}

					// 默认转换为字符串
					row[col] = string(b)
				} else {
					// 检查是否为int64类型，如果是则转换为字符串
					if i64, ok := val.(int64); ok && (i64 > 9007199254740991 || i64 < -9007199254740991) {
						// 超出JavaScript安全整数范围
						row[col] = strconv.FormatInt(i64, 10)
					} else {
						row[col] = val
					}
				}
			} else {
				row[col] = nil
			}
		}
		result = append(result, row)
	}

	return &models.QueryResult{
		Columns:     columns,
		Rows:        result,
		RowCount:    len(result),
		ExecuteTime: time.Since(start),
	}, nil
}

// ExecuteNonQuery 执行非查询语句
func (p *PostgreSQLDriver) ExecuteNonQuery(ctx context.Context, query string, args ...interface{}) (int64, error) {
	if !p.IsConnected() {
		return 0, ErrNotConnected
	}

	result, err := p.DB.ExecContext(ctx, query, args...)
	if err != nil {
		return 0, errors.Wrap(err, "failed to execute non-query")
	}

	affected, err := result.RowsAffected()
	if err != nil {
		return 0, errors.Wrap(err, "failed to get affected rows")
	}

	return affected, nil
}

// BeginTransaction 开始事务
func (p *PostgreSQLDriver) BeginTransaction(ctx context.Context) (DatabaseTransaction, error) {
	if !p.IsConnected() {
		return nil, ErrNotConnected
	}

	tx, err := p.DB.BeginTx(ctx, nil)
	if err != nil {
		return nil, errors.Wrap(err, "failed to begin transaction")
	}

	return &PostgreSQLTransaction{tx: tx}, nil
}

// PostgreSQLTransaction PostgreSQL事务实现
type PostgreSQLTransaction struct {
	tx *sql.Tx
}

func (pt *PostgreSQLTransaction) Commit() error {
	return pt.tx.Commit()
}

func (pt *PostgreSQLTransaction) Rollback() error {
	return pt.tx.Rollback()
}

func (pt *PostgreSQLTransaction) ExecuteQuery(ctx context.Context, query string, args ...interface{}) (*models.QueryResult, error) {
	start := time.Now()
	rows, err := pt.tx.QueryContext(ctx, query, args...)
	if err != nil {
		return &models.QueryResult{
			Error:       err.Error(),
			ExecuteTime: time.Since(start),
		}, nil
	}
	defer rows.Close()

	columns, err := rows.Columns()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get columns")
	}

	// 获取列类型
	colTypes, err := rows.ColumnTypes()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get column types")
	}

	var result []map[string]interface{}
	for rows.Next() {
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))
		for i := range values {
			valuePtrs[i] = &values[i]
		}

		if err := rows.Scan(valuePtrs...); err != nil {
			return nil, errors.Wrap(err, "failed to scan row")
		}

		row := make(map[string]interface{})
		for i, col := range columns {
			val := values[i]

			// 处理特殊类型
			if val != nil {
				// 处理byte数组
				if b, ok := val.([]byte); ok {
					// 尝试确定数据类型
					if colTypes != nil && i < len(colTypes) {
						// 检查数据库列类型
						dbType := strings.ToLower(colTypes[i].DatabaseTypeName())

						// 对于BIGINT类型，转换为字符串以避免JS中的整数溢出
						if strings.Contains(dbType, "int8") {
							row[col] = string(b)
							continue
						}

						// 对于DECIMAL/NUMERIC类型，也转换为字符串保留精度
						if strings.Contains(dbType, "numeric") || strings.Contains(dbType, "decimal") {
							row[col] = string(b)
							continue
						}
					}

					// 默认转换为字符串
					row[col] = string(b)
				} else {
					// 检查是否为int64类型，如果是则转换为字符串
					if i64, ok := val.(int64); ok && (i64 > 9007199254740991 || i64 < -9007199254740991) {
						// 超出JavaScript安全整数范围
						row[col] = strconv.FormatInt(i64, 10)
					} else {
						row[col] = val
					}
				}
			} else {
				row[col] = nil
			}
		}
		result = append(result, row)
	}

	return &models.QueryResult{
		Columns:     columns,
		Rows:        result,
		RowCount:    len(result),
		ExecuteTime: time.Since(start),
	}, nil
}

func (pt *PostgreSQLTransaction) ExecuteNonQuery(ctx context.Context, query string, args ...interface{}) (int64, error) {
	result, err := pt.tx.ExecContext(ctx, query, args...)
	if err != nil {
		return 0, errors.Wrap(err, "failed to execute non-query in transaction")
	}

	affected, err := result.RowsAffected()
	if err != nil {
		return 0, errors.Wrap(err, "failed to get affected rows in transaction")
	}

	return affected, nil
}

// Exec 执行SQL命令，返回sql.Result
func (pt *PostgreSQLTransaction) Exec(query string, args ...interface{}) (sql.Result, error) {
	if pt.tx == nil {
		return nil, errors.New("transaction is nil or already committed")
	}

	return pt.tx.Exec(query, args...)
}

// GetGormDB 返回GORM数据库实例
func (p *PostgreSQLDriver) GetGormDB() *gorm.DB {
	return p.gormDB
}
